<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"
    
    %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@192.168.3.24:1521:xe";
String id = "test";
String pwd = "test";
String driver = "oracle.jdbc.driver.OracleDriver";
String query = " SELECT DEPTLEVEL,DEPTNAME, DEPTNO, DEPTSTEP, "
			 + " DEPTSTDATE, DEPTENDATE, NVL(DEPTPRCODE,' ') AS DEPTPRCODE, DEPTCHIEF, ETC "
	         + " FROM DEPTINFO "
	         + " ORDER BY DEPTLEVEL ";

try{
	Class.forName(driver);
	conn = DriverManager.getConnection(url,id,pwd);
	pstmt = conn.prepareStatement(query);
	rs = pstmt.executeQuery();
	StringBuffer sb=new StringBuffer("<Grid><DATA>");
	while(rs.next()){
	    sb.append("<TR Level=\""+rs.getString(1)+"\">");
		System.out.println("Level= "+rs.getString(1));

		sb.append("<TD><![CDATA[]]></TD>");
		sb.append("<TD><![CDATA[]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(2) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(3) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(1) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(4) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(5) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(6) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(7) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(8) + "]]></TD>");
		sb.append("<TD><![CDATA[" + rs.getString(9) + "]]></TD>");
		sb.append("</TR>");
	}
	sb.append("</DATA>");		
	sb.append("</Grid>");
	out.println(sb.toString());
	//JSON 문자열 생성

}catch(Exception ex){
	String msg = ex.getMessage();
	//오류 발생
	out.println("{");
	out.println("Result : {Code:-1, Message:'오류가 발생하였습니다.\n"+msg+"'}");
	out.println("}");
	
}finally{
	rs.close();
	pstmt.close();
	conn.close();	
}
%>